Skip Headers

Oracle® Objects for OLE Developer's Guide 10g Release 2 (10.2) Part Number B14309-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 9 Server Objects

This chapter describes the Oracle Objects for OLE Server Objects.

See Also: "Overview of Oracle Objects for OLE" "Oracle Objects for OLE In-Process Automation Server" "Using Automation Clients Overview" "Required Setups"

This chapter contains these topics:

- OraAQ Object

- OraAQAgent Object

- OraAQMsg Object

- OraAttribute Object

- OraBFILE Object

- OraBLOB, OraCLOB Objects

- OraClient Object

- OraCollection Object

- OraConnection Object

- OraDatabase Object

- OraDynaset Object

- OraField Object

- OraIntervalDS Object

- OraIntervalYM Object

- OraMDAttribute Object

- OraMetaData Object

- OraNumber Object

- OraObject Object

- OraParamArray Object

- OraParameter Object

- OraRef Object

- OraServer Object

- OraSession Object

- OraSQLStmt Object

- OraSubscription Object

- OraTimeStamp Object

- OraTimeStampTZ Object

- OraConnections Collection

- OraFields Collection

- OraParameters Collection

- OraSessions Collection

- OraSubscriptions Collection

## OraAQ Object

Description

An `OraAQ` object is instantiated by invoking the `CreateAQ` method of the `OraDatabase` interface. It represents a queue that is present in the database.

Remarks

Oracle Objects for OLE provides interfaces for accessing Oracle Database Advanced Queuing (AQ) feature. It makes AQ accessible from popular COM-based development environments such as Visual Basic.

The `OraAQ` Automation interface provides methods for enqueuing and dequeuing messages (encapsulated in the `OraAQMsg` object). It also provides a method for monitoring queues for message arrivals.

Client applications provide a `Dispatch` interface to the monitor. The monitor checks the queue for messages that meet the application criteria. It then invokes the `NotifyMe` method of the `Dispatch` interface when these messages are dequeued.

The following diagram illustrates the OO4O AQ Automation objects and their properties.

Description of the illustration o4o00016.gif

Properties

- Consumer (OraAQ) Property

- Correlate (OraAQ) Property

- DequeueMode (OraAQ) Property

- DequeueMsgId (OraAQ) Property

- Navigation (OraAQ) Property

- RelMsgId (OraAQ) Property

- Visible (OraAQ) Property

- Wait (OraAQ) Property

Methods

- AQMsg (OraAQ) Method

- Enqueue (OraAQ) Method

- Dequeue (OraAQ) Method

- MonitorStart (OraAQ) Method

- MonitorStop (OraAQ) Method

Examples

Example: Enqueuing Messages

Enqueuing messages of type RAW

"Enqueuing Messages of Type RAW"

Enqueuing messages of Oracle object types

"Enqueuing Messages of Oracle Object Types"

Example: Dequeuing messages

NOTE: The following code samples serve as models for dequeuing messages.

A complete AQ sample can be found in `\OO4O\VB\SAMPLES\AQ`

Dequeuing messages of the RAW type

"Example: Dequeuing Messages of RAW Type"

Dequeuing messages of Oracle object types

"Example: Dequeuing Messages of Oracle Object Types"

Example: Monitoring messages

See "Monitoring Messages" for examples illustrating the use of the `MonitorStart` and `MonitorStop` methods.

See Also: OraAQAgent Object OraAQMsg Object Oracle Streams Advanced Queuing User's Guide and Reference for a detailed description of Oracle Advanced Queuing

## OraAQAgent Object

Description

The `OraAQAgent` object represents a message recipient and is only valid for queues that allow multiple consumers.

Remarks

An `OraAQAgent` object can be instantiated by invoking the `AQAgent` method. For example:

    Set agent = qMsg.AQAgent(name)

Methods

None.

Properties

- Address (OraAQAgent) Property

- Name (AQAgent) Property

Example

The following Visual Basic example illustrates a simple use of the advanced queuing feature. A message of a user-defined type, `MESSAGE_TYPE`, is enqueued into a queue, `msg_queue`, that supports multiple consumers.

    Dim q as OraAQ
    Dim qMsg as OraAQMsg
    Dim agent as OraAQAgent
    Set q = OraDatabase.CreateAQ("msg_queue")
    Set qMsg = q.AQMsg(1,"MESSAGE_TYPE")

    'To add SCOTT as a recipient for the message,
    Set agent = qMsg.AQAgent("SCOTT")

    'To enqueue,
    q.Enqueue

See Also: Oracle Streams Advanced Queuing User's Guide and Reference for a detailed description of Oracle Advanced Queuing OraAQ Object OraAQMsg Object

## OraAQMsg Object

Description

The `OraAQMsg` object encapsulates the message to be enqueued or dequeued. The message can be of any user-defined or raw type.

Properties

- Correlation (OraAQMsg) Property

- Delay (OraAQMsg) Property

- ExceptionQueue Property

- Expiration (OraAQMsg) Property

- Priority (OraAQMsg) Property

- Value (OraAQMsg) Property

Methods

- AQAgent (OraAQMsg) Method

See Also: Oracle Streams Advanced Queuing User's Guide and Reference for a detailed description of Oracle Advanced Queuing OraAQ Object OraAQAgent Object

## OraAttribute Object

Description

The `OraAttribute` object represents an attribute of a `Value` or `REF` instance of an `OraObject` or an `OraRef`.

Remarks

The `OraAttribute` object can be accessed from the `OraObject` or `OraRef` object by creating a subscript that uses ordinal integers or by using the name attribute.

See the `Value` (`OraAttribute`) property for a table that identifies the attribute type and the return value of the `Value` property of the `OraAttribute` object:

Properties

- Value (OraAttribute) Property

- Name (OraAttribute) Property

- Type (OraAttribute) Property

Methods

None.

Examples

The following example accesses the attributes of the `ADDRESS` value instance in the server. Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in the OraObject and OraRef Examples".

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset
    Dim Address As OraObject
    Dim City As OraAttribute
    Dim State As OraAttribute

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from person_tab
    Set OraDynaset = OraDatabase.CreateDynaset("select * from person_tab", 0&)

    'retrieve an address column from person_tab
    'the Value property of OraField object returns Address OraObject
    Set Address = OraDynaset.Fields("Addr").Value

    'access the City attribute object
    Set City = Address("City")

    ' display the value of City attribute Object
    MsgBox City.Value

    'access the State attribute object
    Set State = Address("State")

    'display the value of State attribute Object
    MsgBox State.Value

See Also: OraCollection Object OraParameter Object OraObject Object OraRef Object

## OraBFILE Object

Description

The `OraBFile` interface in OO4O provides methods for performing operations on the `BFILE` LOB data type in the database.

Remarks

The `BFILE` types are large binary data objects stored in operating system files (external) outside of the database tablespaces.

Properties

- DirectoryName Property

- FileName Property

- Exists Property

- IsNull (OraLOB/BFILE) Property

- IsOpen (OraBFILE) Property

- Offset (OraLOB/BFILE) Property

- PollingAmount Property

- Size (OraLOB and OraBFILE) Property

- Status (OraLOB/BFILE) Property

Methods

- Clone (OraLOB/BFILE) Method

- Close (OraBFILE) Method

- CloseAll (OraBFILE) Method

- Compare (OraLOB) Method

- CopyToFile (OraLOB/BFILE) Method

- MatchPos (OraLOB/BFILE) Method

- Open (OraBFILE) Method

- Read (OraLOB/BFILE) Method

Examples

See "Schema Objects Used in LOB Data Type Examples" for schema objects that are used in the OraLOB/`BFILE` examples.

NOTE: To add the required tables for the following examples, run the `lob.sql` file in the `\OO4O\VB\SAMPLES\LOB` directory.

Example: Accessing the BFILE Value

`BFILE` data can be read using the `Read` method. The `OraBFILE` object allows piecewise read operations. Before reading the `BFILE` content, the `BFILE` file should be opened using the `Open` method.

    Dim PartColl as OraBFile
    Dim buffer As Variant

    'Create a Dynaset containing a BLOB and a CLOB column
    set part = OraDatabase.CreateDynaset ("select * from part",0)
    Set PartColl = part.Fields("part_collateral").Value

    'open the bfile for read operation
    PartColl.Open

    'read the entire bfile
    amount_read = PartColl.Read(buffer)

    'close the bfile
    PartColl.Close

Example: Reading and Inserting BFILEs Using Dynasets

To modify the directory and file names of the `BFILE` value of an `OraBFILE` object, first obtain a lock and then use the `DirectoryName` and `FileName` properties.

To insert a new row containing a `BFILE` column, initialize the `BFILE` column with new directory and file name values using the `DirectoryName` and `FileName` properties.

    Dim PartColl as OraBFile
    Dim buffer As Variant

    'Create a Dynaset containing a BLOB and a CLOB column
    set part = OraDatabase.CreateDynaset ("select * from part",0)
    Set PartColl = part.Fields("part_collateral").Value

    'insert a new BFILE in the part_collateral column
    part.AddNew

    'Directory objects will be upper-case by default
        PartColl.DirectoryName = "NEWDIRECTORYNAME"
        PartColl.FileName = "NewPartCollatoral"
    part.Update

    'move to the newly added row
    part.MoveLast

    'open the Bfile for read operation
    PartColl.Open

    'read the entire bfile
    amount_read = PartColl.Read(buffer)

    'close the Bfile
    PartColl.Close

See Also: OraBLOB, OraCLOB Objects Oracle Database Application Developer's Guide - Large Objects for a detailed description of Oracle `BFILE` types

## OraBLOB, OraCLOB Objects

Description

The `OraBLOB` and `OraCLOB` interfaces in OO4O provide methods for performing operations in a database on the large object data types `BLOB`, `CLOB`, and `NCLOB`. In this developer's guide, `BLOB`, `CLOB`, and `NCLOB` data types are also referred to as LOB data types.

OO4O supports the creation of temporary `BLOB` or `CLOB` types that can be manipulated and then bound to SQL statements or PL/SQL blocks, or copied into permanent LOBs.

Remarks

LOB data is accessed using the `Read` and `CopyToFile` methods.

LOB data is modified using the `Write`, `Append`, `Erase`, `Trim`, `Copy`, `CopyFromFile`, and `CopyFromBFile` methods. A row lock must be obtained before modifying the contents of a LOB column in a row. If the LOB column is a field of an `OraDynaset` object, then the lock is obtained by invoking the `Edit` method.

None of the LOB operations are allowed on `NULL` LOBs. To avoid errors, use the `IsNull` property to detect `NULL` LOBs. To perform write operations on a LOB that is null, first the LOB column must be initialized with an Empty value.

To insert a new row having a LOB column, first initialize the LOB column with an Empty value by setting the `Value` property of the `OraField` or `OraParameter` object to the keyword Empty and commit the change to the database. The newly updated Empty LOB must be selected again from the database before it can be used. This is done automatically in the case of the `OraDynaset` object: If a LOB field in an `OraDynaset` object is set to Empty and the `Update` method is called, OO4O automatically reselects the Empty LOB into the dynaset making it available for use in subsequent write operations.

There are two modes of operation for read and write operations for LOBs.

1. Multiple-piece read/write operations

   In this mode, the total amount of data to be read or written is more than the size of the buffer for an individual read/write operation. Rather than make a complete round-trip for each operation, the pieces are streamed. To begin the multiple piece operation, the `PollingAmount` property is first set to the total amount of data to be read or written. The `Offset` property is set at this time to specify the initial offset for the first piece read/write operation. The offset is automatically incremented after the first read/write operation, and cannot be changed again until the multiple piece operation has completed. The `Status` property must be checked for the success of each piecewise operation and the operation must continue until all the pieces are read or written (it cannot be aborted). To start another multiple-piece read/write operation on the same LOB, the `PollingAmount` property has to be reset to the desired amount. See "Example: Multiple-Piece Read of a LOB".

2. Single-piece read/write operation

   In this mode, the reading and writing of data occurs in one operation. This mode is enabled when the `PollingAmount` property is set to `0`. See "Example: Single-Piece Read of a LOB".

   The `Offset` property in both modes of operation is 1-based.

By design, LOBs cannot span transactions started by `SELECT` `..` `FOR` `UPDATE`, `INSERT`, and `UPDATE` statements. Selecting or modifying LOB values using these SQL statements makes LOBs invalid outside the current transaction. In Oracle Objects for OLE, transactions can be started and ended in the following ways.

1. `Dynaset` `Edit`/`Update` method

   The `Edit` method executes the `SELECT` `FOR` `UPDATE` statement to lock the row and start the transaction. The `Update` method ends the transaction. If the LOB column value is modifed between the `Edit` and `Update` pair, OO4O reselects the value of LOB column after the `Update` call. This is transparent to the user. Note that OO4O does not reselect the LOB value if the LOB is an attribute of an Oracle objects instance or element of an Oracle collection. If the transaction is started by the `OraSession`/`OraDatabase` or `OraServer` object and the LOB data is modified between the `Edit` and `Update` methods, OO4O does not reselect the LOB value from the database. LOBs are invalid after committing transactions initiated by `OraSession`/`OraDatabase` or `OraServer` objects.

   See "Example: Dynasets Containing LOBs and Transactions".

2. Executing an `INSERT` or `UPDATE` statement through the `ExecuteSQL` or `CreateSQL` method.

   An `INSERT` or `UPDATE` statement starts the transaction, and the transaction is implicitly ended by Oracle Objects for OLE (auto-commit). If a statement has a LOB output bind parameter, as in the case of the `RETURNING` `..` `INTO` clause, then it will become invalid after the `ExecuteSQL` or `CreateSQL` method is executed To avoid this, the user must execute these statement between the `BeginTrans`/`CommitTrans` pair of `OraSession`, `OraServer` or `OraDatabase` objects.

   See "Example: INSERT or UPDATE Statements with LOBs and Transactions".

See Also: "Using Large Objects (LOBs)" for more information about LOB operations and LOB performance issues Oracle Database Application Developer's Guide - Large Objects for a detailed description of Oracle LOBs

Properties

- IsNull (OraLOB/BFILE) Property

- PollingAmount Property

- Offset (OraLOB/BFILE) Property

- Size (OraLOB and OraBFILE) Property

- Status (OraLOB/BFILE) Property

Methods

- Append (OraLOB) Method

- Clone (OraLOB/BFILE) Method

- Compare (OraLOB) Method

- Copy (OraLOB) Method

- CopyFromFile (OraLOB) Method

- CopyFromBFILE (OraLOB) Method

- CopyToFile (OraLOB/BFILE) Method

- DisableBuffering (OraLOB) Method

- EnableBuffering (OraLOB) Method

- Erase (OraLOB) Method

- FlushBuffer (OraLOB) Method

- MatchPos (OraLOB/BFILE) Method

- Read (OraLOB/BFILE) Method

- Trim (OraLOB) Method

- Write (OraLOB) Method

Examples

See "Schema Objects Used in LOB Data Type Examples" for schema objects that are used in the OraLOB and `BFILE` examples.

Example: Accessing a LOB Value

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset
    Dim PartImage as OraBlob
    Dim buffer As Variant

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", _
                               "scott/tiger", 0&)

    'execute the select statement
    set OraDynaset = OraDatabase.CreateDynaset ("select * from part",0&)

    'retrieve photo field from the dynaset
    set PartImage = OraDynaset.Fields("part_image").Value

    'read the entire LOB column in one piece into the buffer
    amount_read = PartImage.Read(buffer, 10)
    'use the buffer for internal processing

Example: Modifying a LOB Value

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset
    Dim  PartDesc as OraClob
    Dim buffer As String

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb","scott/tiger", 0&)

    'execute the select statement
    set OraDynaset = OraDatabase.CreateDynaset ("select * from part",0&)
    set PartDesc = OraDynaset.Fields("part_desc").Value

    'To get a free file number
    FNum = FreeFile
    'Open the file for reading
    Open "partdesc.dat" For Binary As #FNum

    'Allocate buffer to the size of file FNum and read the entire file
    buffer = String$(LOF(FNum), 32)
    Get #FNum, , buffer

    'lock the row for write operation
    OraDynaset.Edit
    amount_written = PartDesc.Write(buffer)

    'commit the operation and release the lock
    OraDynaset.Update
    Close FNum

Example: Inserting LOBs Using Dynasets

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim Part As OraDynaset
    Dim PartImage as OraBLOB
    Dim ImageChunk() As Byte
    Dim amount_written As Long

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'Create a Dynaset containing a BLOB and a CLOB column

    set part = OraDatabase.CreateDynaset ("select * from part",0)
    set PartImage = part.Fields("part_image").Value

    'First insert Empty LOB in the part_image column
    part.AddNew
        part.Fields("part_id").Value = 1234
        part.Fields("part_image").Value = Empty
    part.Update

    'move to the newly added row
    Part.MoveLast

    'To get a free file number
    FNum = FreeFile

    'Open the file for reading PartImages
    Open "part_picture.gif" For Binary As #FNum

    'Re adjust the buffer size to hold entire file data

    Redim ImageChunk(LOF(FNum))

    'read the entire file and put it into buffer
    Get #FNum, , ImageChunk

    'call dynaset's Edit method to lock the row
    part.Edit
    amount_written = OraBlob.Write(ImageChunk)
    part.Update

    'close the file
    Close FNum

Example: Inserting LOBs Using an OraParameter Object

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraBlob As OraBlob
    Dim ImageChunk() As Byte
    Dim amount_written As Long

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
    Set OraParameters = OraDatabase.Parameters
    OraParameters.Add "PartImage", Empty,ORAPARM_OUTPUT
    OraParameters("PartImage").ServerType = ORATYPE_BLOB

    'BeginTrans needs to be called since LOB locators become
    'invalid after the ExecuteSQL call
    OraSession.BeginTrans
    OraDatabase.ExecuteSQL ("insert into part values (1234,'Oracle  Application'," & _
                    "EMPTY_BLOB(),NULL,NULL) RETURNING part_image INTO :PartImage")
    set PartImage = OraDatabase.Parameters("PARTIMAGE").Value

    FNum = FreeFile
    'Open the file for reading PartImages
    Open "part_picture.gif" For Binary As #FNum

    'read the file and put it into buffer
    Redim ImageChunk(LOF(FNum))
    Get #FNum, , ImageChunk

    Set OraBlob = OraDatabase.Parameters("PartImage").Value
    amount_written = OraBlob.Write(ImageChunk, 10, ORALOB_ONE_PIECE)

    ' commit the transaction and close the file
    OraSession.CommitTrans
    Close FNum

Example: Dynasets Containing LOBs and Transactions

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraBlob As OraBlob
    Dim PartImage as OraBLOB
    Dim ImageChunk() As Byte
    Dim amount_written As Long

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb",  "scott/tiger", 0&)

    'Create a Dynaset containing a BLOB and a CLOB column
    set part = OraDatabase.CreateDynaset ("select * from part " & _
                                  "where part_id = 1234",0)
    set PartImage = part.Fields("part_image").Value

    'To get a free file number
    FNum = FreeFile

    'Open the file for reading PartImages
    Open "c:\part_picture.gif" For Binary As #FNum
    Redim ImageChunk(LOF(FNum))

    'read the file and put it into buffer
    Get #FNum, , ImageChunk

    'starts the transaction on OraSession
    OraSession.BeginTrans

    'call dynaset's Edit method to lock the row
    part.Edit
    Set OraBlob = PartImage
    amount_written = OraBlob.Write(ImageChunk, 10, ORALOB_ONE_PIECE)
    part.Update

    'ends the transaction
    OraSession.CommitTrans

    'the following lines of code will raise error
    'LOB locator cannot span transaction'
    msgbox Partimage.Size
    Close FNum

Example: INSERT or UPDATE Statements with LOBs and Transactions

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim ImageChunk() As Byte
    Dim amount_written As Long

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    Set OraParameters = OraDatabase.Parameters
    OraParameters.Add "PartImage", Empty,ORAPARM_OUTPUT
    OraParameters("PartImage").ServerType = ORATYPE_BLOB

    'Create a Dynaset containing a LOB,column
    OraDatabase.ExecuteSQL ("insert into part values (1234,'Oracle Application'," & _
                      "EMPTY_BLOB(),NULL,NULL) RETURNING part_image INTO :PartImage")
    set PartImage = OraDatabase.Parameters("PARTIMAGE").Value

    'the following lines of code will raise error
    'LOB locator cannot span transaction'
    msgbox Partimage.Size

Example: Using the CopyToFile Method

See "Example:Using the CopyToFile Method".

Example: Using the CopyFromFile Method

See "Example: Using the CopyFromFile Method".

Example: Multiple-Piece Read of a LOB

See "Example: Multiple-Piece Read of a LOB".

Example: Single-Piece Read of a LOB

See "Example: Single-Piece Read of a LOB".

Example: Multiple-Piece Write of a LOB

See "Multiple-Piece Write of a LOB Example".

Example: Single-Piece Write of a LOB

See "Single-Piece Write of a LOB Example".

Example: Passing a Temporary CLOB to a Stored Procedure

See "Example: Passing a Temporary CLOB to a Stored Procedure".

See Also: OraBFILE Object CreateTempBLOB/CLOB Method

## OraClient Object

Description

An `OraClient` object defines a workstation domain, and all of the `OraSession` objects of that workstation are listed in the `OraSessions` collection of the `OraClient` object.

Remarks

Only one `OraClient` object can exist for each workstation, and it is created automatically by the system when it is needed.

Properties

- Name Property

- Sessions Property

Methods

- CreateSession Method

See Also: OraSession Object OraSessions Collection

## OraCollection Object

Description

The `OraCollection` interface represents Oracle collection types, such as variable-length arrays (`VARRAY`s) and nested tables.

Remarks

A collection is an ordered group of elements, all of the same type. For example, the students in a class or the grades for each student in a class. Each element has a unique subscript, called an index, that determines its position in the collection.

The collection type nested table is viewed as a table stored in the column of database tables. When retrieved, rows of a nested table are given consecutive subscripts that start at `1`. Individual rows are accessed using an array-like access.

The collection type `VARRAY` is viewed as an array stored in the column of database tables. To reference an element in a `VARRAY` data type, standard subscripting syntax can be used. For example, `Grade(3)` references the third element in the `VARRAY` data type named `Grades`.

The `OraCollection` provides methods for accessing and manipulating an Oracle collection. Implicitly an `OraCollection` object contains an OLE Automation collection interface for accessing and manipulating (updating and inserting) individual elements of an Oracle collection. Individual elements can be accessed by using a subscript. An `OraCollection` element index starts at 1.

Element values are retrieved as `Variant` types. The `Variant` type of the element depends on the element type of the collection. Element values can be `Null` and can be set to `Null`. For elements of type objects and `REF`s, element values are returned as corresponding OO4O objects for that type. `VARRAY`s and nested tables do not support the elements of LOBs, `VARRAY`s, and Nested tables.

Table 9-1 lists the element type and return value of the elements.

Table 9-1 Element Type and Return Value of Elements

Element Type | Element Value
--- | ---
`Object` | `OraObject`
`REF` | `OraRef`
`Date` | `String`
`Number` | `String`
`CHAR`, `VARCHAR2` | `String`
`Real` | `Real`
`Integer` | `Integer`

Element values are converted into a `Variant` `SAFEARRAY` format using the `SafeArray` property. Only elements of primitive types are supported. A `Variant` `SAFEARRAY` index starts at `0`.

The `CreateOraObject` method on the `OraDatabase` object returns the `OraCollection` object. The Oracle collection associated with this `OraCollection` object is created in the client-side object cache.

For information about creating a dynaset from a collection, see to "Creating a Dynaset from an OraCollection Object".

Properties

- BOC Property

- ElementType Property

- EOC Property

- IsLocator (OraCollection) Property

- IsNull (OraCollection) Property

- MaxSize (OraCollection) Property

- SafeArray (OraCollection) Property

- Size (OraCollection) Property

- TableSize (OraCollection) Property

- Type (OraCollection) Property

Methods

- Append (OraCollection) Method

- Clone (OraCollection) Method

- CreateIterator Method

- Delete (OraCollection) Method

- DeleteIterator Method

- ElementValue Method

- Exist (OraCollection) Method

- InitIterator Method

- IterNext Method

- IterPrev Method

- Trim (OraCollection) Method

Examples

Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in OraCollection Examples" for schema objects that are used in the `OraCollection` examples.

Example: Accessing Collection Elements

The following example illustrates how to access collection elements.

OraDynaset Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim EnameList as OraCollection

    'create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb","scott/tiger", 0&)

    'create a dynaset object from department
    set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)

    'retrieve a Enames column from Department.
    'Here Value property of OraField object returns EnameList OraCollection
    set EnameList = OraDynaset.Fields("Enames").Value

    'access the first element of EnameList
    msgbox  EnameList(1)

    'move to next to row
    OraDynaset.MoveNext

    'access all the elements of EnameList for the second row
    For index = 1 To EnameList.Size
            msgbox EnameList(index)
    Next Index

OraParameter Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim EnameList as OraCollection

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create an  OraParameter object representing EnameList collection bind Variable
    OraDatabase.Parameters.Add "ENAMES", Null, ORAPARM_OUTPUT, _
                     ORATYPE_VARRAY,"ENAMELIST"

    'execute the sql statement which selects ENAMES VARRAY  from the department  table

    OraDatabase.ExecuteSQL ("BEGIN select enames  into :ENAMES  from department " & _
                       "where  dept_id = 10;  END;")

    'get the EnameList collection  from OraParameter
    set EnameList = OraDatabase.Parameters("ENAMES").Value

    'access all the elements of EnameList
    For index = 1 To EnameList.Size
            msgbox EnameList(index)
    Next Index

Example: Modifying Collection Elements

The following example illustrates how to modify collection elements.

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim EnameList as OraCollection

    'create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from department
    set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)

    'retrieve a Enames column from Department. Here Value property of OraField object
    'returns EnameList OraCollection

    set EnameList = OraDynaset.Fields("Enames").Value

    'lock the row for editing and set the 2nd element of the EnameList to new value
    OraDynaset.Edit
    EnameList(2) = "Eric"
    OraDynaset.Update

Example: Inserting in a Collection

The following example illustrates how to insert elements into an Oracle collection.

OraDynaset Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim EnameListNew as OraCollection

    'create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a new OraCollection object from the database
    set EnameListNew = OraDatabase.CreateOraObject("ENAMELIST")

    'set EnameListNew's element values
    EnameListNew(1)  = "Nasser"
    EnameListNew(2) =  "Chris"
    EnameListNew(3) =  "Gopal"

    'create a dynaset object from department
    set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)

    'start the AddNew operation and insert the EnameListNew collection
    OraDynaset.AddNew
    OraDynaset.Fields("dept_id") = 40
    OraDynaset.Fields("name") = "DEVELOPMENT"

    'set the EnameListNew to enames column
    OraDynaset.Fields("enames") = EnameListNew
    OraDynaset.Update

OraParameter Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim EnameListNew as OraCollection

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a new OraCollection object from the database
    set EnameListNew = OraDatabase.CreateOraObject("ENAMELIST")

    'set EnameListNew's element values
    EnameListNew(1)  = "Nasser"
    EnameListNew(2) =  "Chris"
    EnameListNew(3) =  "Gopal"

    'create an input OraParameter object representing EnameList collection bind 'Variable

    OraDatabase.Parameters.Add "ENAMES", Null, ORAPARM_INPUT, ORATYPE_VARRAY, _
                    "ENAMELIST"

    'set the ENAMES parameter value to EnameListNew
    OraDatabase.Parameters("ENAMES").Value  = EnameListNew

    'execute the insert sql statement
    OraDatabase.ExecuteSQL ("insert into department values (40,'DEVELOPMENT', " & _
                  ":ENAMES)")

Example: Collection with Object Type Elements

The following example illustrates the use of an Oracle collection having elements of object type.

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim CourseList as OraCollection
    Dim Course as OraObject

    'create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from division
    set OraDynaset = OraDatabase.CreateDynaset("select * from division", 0&)

    'retrieve a Courses column from Division.
    'Here Value property of OraField object returns CourseList OraCollection

    set CourseList = OraDynaset.Fields("Courses").Value
    'retrieve the element value of the CourseList at index 1.
    'Here element value is returned as Course OraObject
    set Course = CourseList(1)

    'retrieve course_no and title attribute of the Course
    msgbox Course.course_no
    msgbox Course.title

    'move to next row
    OraDynaset.MoveNext

    'now CourseList object represents collection value for the second row
    'and course OraObject 'represents the element value at index 1.
    'retrieve course_no and title attribute of the Course.
    msgbox Course.course_no
    msgbox Course.title

Example: Creating a SAFEARRAY Variant from a Collection

The following example illustrates how to get and set a SAFEARRAY `Variant` with an Oracle collection.

Creating SAFEARRAY Variant from a Collection

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim EnameList as OraCollection
    Dim EnameArray as Variant

    'create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from department
    set OraDynaset = OraDatabase.CreateDynaset("select * from department", 0&)

    'retrieve a Enames column from Department.
    'Here Value property of OraField objectreturns EnameList OraCollection
    set EnameList = OraDynaset.Fields("Enames").Value

    'get the Variant SAFEARRAY from the collection.
    EnameArray = EnameList.SafeArray

    'display the individual elements of EnameArray
    msgbox EnameArray(0)
    msgbox EnameArray(1)
    msgbox EnameArray(2)

Setting SAFEARRAY Variant to the Collection

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim EnameList as OraCollection
    Dim EnameArray() As String
    ReDim EnameArray(3)

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create an Empty uninitialized input OraParameter object
    'represent EnameList collection bind Variable
    OraDatabase.Parameters.Add "ENAMES", Empty, ORAPARM_INPUT, _
                                ORATYPE_VARRAY,"ENAMELIST"

    'get the Empty uninitialized ENAMES parameter value
    set EnameList = OraDatabase.Parameters("ENAMES").Value

    'initialize the EnameArray
    EnameArray(0) = "Nasser"
    EnameArray(1) = "Chris"
    EnameArray(2) = "Gopal"

    'set the EnameArray to EnameList's SafeArray
    EnameList.SafeArray = EnameArray

    'execute the insert sql statement
    OraDatabase.ExecuteSQL ("insert into department " & _
                    "values (40,'DEVELOPMENT', :ENAMES)")

Example: Creating a Dynaset from a Collection

The following example illustrates how to create a dynaset from an Oracle collection.

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim CourseList as OraCollection
    Dim Course as OraObject
    Dim CourseListDyn as OraDynaset

    'create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger",  0&)

    'create a dynaset object from division
    set OraDynaset = OraDatabase.CreateDynaset("select * from division", 0&)

    'retrieve a Courses column from Division. Here Value
    'property of OraField object returns CourseList OraCollection
    set CourseList = OraDynaset.Fields("Courses").Value

    'create a input parameter for CourseList for nested table dynaset
    OraDatabase.Parameters.Add "COURSELIST", CourseList, ORAPARM_INPUT, _
                                ORATYPE_TABLE, "COURSELIST"

    'create a read only dynaset based on the CourseList.
    Set CourseListDyn = OraDatabase.CreateDynaset("select * from THE" & _
              "(select CAST(:COURSELIST AS COURSELIST) from dual)", ORADYN_READONLY)

    'dynaset can also be created from Oracle8 collection
    'using the following statement, which requires OO4O v8.1.x later

    Set CourseListDyn = OraDatabase.CreateDynaset("select * from " & _
               "TABLE(CAST(:COURSELIST AS COURSELIST))", ORADYN_READONLY)

    'get the field values of the collection dynaset
    msgbox CourseListDyn.Fields("title").Value
    msgbox CourseListDyn.Fields("course_no").Value

    'move the original dynaset to second row
    Oradynaset.MoveNext

    'set the new value of CourseList  collection from the second row of main dynaset
    'to the "COURSELIST" parameter
    OraDatabase.Parameters("COURSELIST").Value = CourseList

    'refresh the collection dynaset. Now the collection dynaset values are refreshed
    ' with new collection value.
    CourseListDyn.Refresh

    'get the field values of the collection dynaset
    msgbox CourseListDyn.Fields("title").Value
    msgbox CourseListDyn.Fields("course_no").Value

Example: Collection Iterator

See "Example: OraCollection Iterator" .

See Also: OraParameter Object CreateOraObject (OraDatabase) Method "Instantiating Oracle LOBs, Objects, and Collections" "Oracle Collections"

## OraConnection Object

Description

An `OraConnection` object represents a single connection to an Oracle database.

Remarks

An `OraConnection` object is created automatically whenever an `OraDatabase` object is instantiated within the session, and it is destroyed automatically whenever all databases using the connection are discarded.

Currently, there is no way to create an `OraConnection` object explicitly, only by creating an `OraDatabase` object that requires a connection.

Properties

- Connect Property

- ConnectionOK Property

- DatabaseName Property

- Session Property

Methods

- BeginTrans Method

- CommitTrans Method

- ResetTrans Method

- Rollback Method

See Also: OraConnections Collection OraDatabase Object

## OraDatabase Object

Description

An `OraDatabase` interface represents a user session to an Oracle database and provides methods for SQL and PL/SQL execution.

Remarks

An `OraDatabase` interface in Oracle8i and higher releases adds additional methods for controlling transactions and creating interfaces representing instances of Oracle object types. Attributes of schema objects can be retrieved using the `Describe` method of the `OraDatabase` interface.

In previous releases, an `OraDatabase` object is created by invoking the `OpenDatabase` method of an `OraSession` interface. The network alias, user name, and password are passed as arguments to this method. In Oracle8i and higher releases, invocation of this method results in implicit creation of an `OraServer` object.

As described in the `OraServer` interface description, an `OraDatabase` object can also be created using the `OpenDatabase` method of the `OraServer` interface.

Transaction control methods are available at the `OraDatabase` (user session) level. These methods include:

- `BeginTrans`

- `CommitTrans`

- `Rollback`

For example:

    MyDatabase.BeginTrans
    MyDatabase.ExecuteSQL("delete from emp where empno = 1234")
    MyDatabase.CommitTrans

Note: If the `AutoCommit` property is set to `True`, transactions are committed automatically, and you do not need to use the transaction control methods.

Properties

- AutoCommit Property

- CacheMaximumSize Property

- CacheOptimalSize Property

- Connect Property

- Connection Property

- ConnectionOK Property

- DatabaseName Property

- LastServerErr Property

- LastServerErrPos Property

- LastServerErrText Property

- Options Property

- Server Property

- Parameters Property

- RDMSVersion Property

- Subscriptions Property

Methods

- BeginTrans Method

- Close Method

- CommitTrans Method

- CreateAQ Method

- CreateCustomDynaset Method

- CreateTempBLOB/CLOB Method

- CreateDynaset Method

- CreateOraObject (OraDatabase) Method

- CreateSQL Method

- Describe Method

- ExecuteSQL Method

- FetchOraRef Method

- LastServerErrReset Method

- MonitorForFailover Method

- Open (OraServer) Method

- RemoveFromPool Method

- Rollback Method

See Also: OpenDatabase Method OraServer Object OraServer Object

## OraDynaset Object

Description

An `OraDynaset` object permits browsing and updating of data created from a SQL `SELECT` statement.

Remarks

An `OraDynaset` object represents the result set of a SQL `SELECT` query or a PL/SQL cursor variable returned from a stored procedure or function. It is essentially a client-side scrollable and updatable cursor that allows browsing the set of rows generated by the query it executes. It is created by the `CreateDynaset` or `CreateCustomDynaset` method of an `OraDatabase` interface. An `OraDynaset` object can be used to scroll result sets that contain instances of relational and object-relational columns such as `VARRAY`s, nested tables, `Object`s, `REF`s, and LOBs and `BFILE` types.

This object provides transparent mirroring of database operations, such as updates. When data is updated through the `Update` method, the local mirror image of the query is updated so that the data appears to have been changed without reevaluating the query. The same procedure is used automatically when records are added to the dynaset. Integrity checking is performed to ensure that the mirrored image of the data always matches the actual data present on Oracle Database. This integrity checking is performed only when necessary (such as just before updates occur).

During create and refresh operations, the `OraDynaset` objects automatically bind all relevant enabled input parameters to the specified SQL statement, using the parameter names as placeholders in the SQL statement. This can simplify dynamic query building and increase the efficiency of multiple queries using the same SQL statement with varying `WHERE` clauses.

When you use Oracle Objects for OLE, locks are not placed on data until an `Edit` method is executed. The `Edit` method attempts to obtain a lock using the `"SELECT` `...` `FOR` `UPDATE"` statement on the current record of the dynaset. This is done as late as possible to minimize the time that locks are placed on the records. The `Edit` method can fail for several reasons:

- The SQL query violates the Oracle SQL update rules; for example, using calculated columns or table joins.

- The user does not have the privileges needed to obtain a lock.

- The record has been locked already by another user. Note that the `OpenDatabase` method has an option so that you can decide whether to wait on locks.

Properties

BOF Property | LastModified Property
Bookmark Property | NoMatch Property
BookMarkable Property | Options Property
CacheBlocks Property | RecordCount Property
CacheChanged Property | RowPosition Property
CacheSliceSize Property | Session Property
CacheSlicesPerBlock Property | SnapShot Property
Connection Property | SQL Property
Database Property | Transactions Property
EditMode Property | Updatable Property
EOF Property | XMLCollID Property
FetchLimit Property | XMLEncodingTag Property
FetchSize Property | XMLNullIndicator Property
FieldIndex Property | XMLOmitEncodingTag Property
FieldName Property | XMLRowsetTag Property
FieldOriginalName Property | XMLRowID Property
FieldOriginalNameIndex Property | XMLRowTag Property
Fields Property | XMLUpperCase Property

Methods

- AddNew Method

- Clone Method

- Close Method

- CopyToClipboard Method

- Delete Method

- Edit Method

- FindFirst, FindLast, FindNext, and FindPrevious Methods

- GetRows Method

- GetXML Method

- GetXMLToFile Method

- MoveFirst, MoveLast, MoveNext, and MovePrevious Methods

- MovePreviousn, MoveNextn, MoveRel, and MoveTo Methods

- Refresh Method

- Update Method

See Also: CreateDynaset Method CreateCustomDynaset Method OraParameter Object RowPosition Property Update Method

## OraField Object

Description

An `OraField` object represents a single column or data item within a row of a dynaset.

Remarks

An `OraField` object is accessed indirectly by retrieving a field from the `OraFields` collection of an `OraDynaset` object.

If the current row is being updated, then the `OraField` object represents the currently updated value, although the value may not yet have been committed to the database.

Assignment to the `Value` property of a field is permitted only if a record is being edited (using the `Edit` method) or a new record is being added (using the `AddNew` method). Other attempts to assign data to the `Value` property of a field results in an error.

Properties

- OraIDataType Property

- OraMaxDSize Property

- OraMaxSize Property

- OraNullOK Property

- OraPrecision Property

- OraScale Property

- Name Property

- Size Property

- Truncated Property

- Type Property

- Value Property

- XMLAsAttribute Property

- XMLTagName Property

Methods

- AppendChunk Method

- AppendChunkByte Method

- FieldSize Method

- GetChunk Method

- GetChunkByteEx Method

- OriginalName

- ReadChunk Method

See Also: AddNew Method Edit Method OraDynaset Object OraFields Collection Value Property

## OraIntervalDS Object

Description

The `OraIntervalDS` object provides methods for operations on the Oracle `INTERVAL` `DAY` `TO` `SECOND`.This data type represents a period of time in terms of days, hours, minutes, seconds, and nanoseconds.

Remarks

The `OraIntervalDS` object is created by the `OraSession.CreateOraIntervalDS` method or by calling the `Clone` method on an existing `OraIntervalDS` object.

An `OraIntervalDS` object can be bound using the `ServerType` `ORATYPE_INTERVALDS`. This allows the binding of a value to a parameter associated with an Oracle `INTERVAL` `DAY` `TO` `SECOND` data type in a SQL or PL/SQL statement.

When binding a string associated with an `INTERVAL` `DAY` `TO` `SECOND` data type, the `ServerType` must be specified to be a string type (for example, `ORATYPE_VARCHAR2`, `ORATYPE_STRING`) and the string must be in the format specified by Day HH:MI:SSxFF.

Properties

- Days Property

- Hours Property

- Minutes Property

- Nanonseconds Property

- Seconds Property

- TotalDays Property

- Value (OraIntervalDS) Property

Methods

- Add (OraIntervalDS) Method

- Clone (OraIntervalDS) Method

- Div (OraIntervalDS) Method

- IsEqual (OraIntervalDS) Method

- IsGreater (OraIntervalDS) Method

- IsLess (OraIntervalDS) Method

- Mul (OraIntervalDS) Method

- Neg (OraIntervalDS) Method

- Sub (OraIntervalDS) Method

- ToOraNumber (OraIntervalDS) Method

See Also: CreateOraIntervalDS Method OraNumber Object ServerType Property

## OraIntervalYM Object

Description

The `OraIntervalYM` object provides methods for operations on the Oracle `INTERVAL` `YEAR` `TO` `MONTH`.This data type represents a period of time in terms of years and months.

Remarks

The `OraIntervalYM` object is created by the `OraSession.CreateOraIntervalYM` method or by calling the `Clone` method on an existing `OraIntervalYM` object.

An `OraIntervalYM` object can be bound using `ServerType` `ORATYPE_INTERVALYM`. This allows the binding of a value to a parameter associated with an Oracle `INTERVAL` `YEAR` `TO` `MONTH` data type in a SQL or PL/SQL statement.

When binding a string associated with an `INTERVAL` `YEAR` `TO` `MONTH` data type, the `ServerType` must be specified to be a string type (for example, `ORATYPE_VARCHAR2,` `ORATYPE_STRING`), and the string must be in the format specified by YEARS-MONTHS.

Properties

- Months Property

- Years Property

- TotalYears Property

- Value Property

Methods

- Add (OraIntervalYM) Method

- Clone (OraIntervalYM) Method

- Div (OraIntervalYM) Method

- IsEqual (OraIntervalYM) Method

- IsGreater (OraIntervalYM) Method

- IsLess (OraIntervalYM) Method

- Mul (OraIntervalYM) Method

- Neg (OraIntervalYM) Method

- Sub (OraIntervalYM) Method

See Also: CreateOraIntervalYM Method OraNumber Object ServerType Property

## OraMDAttribute Object

Description

Each `OraMDAttribute` object describes an individual attribute. It represents an entry to the attribute table of the `OraMetaData` object. It can be accessed by creating a subscript that uses ordinal integers or by using the name of the attribute.

Remarks

None.

Properties

- Name (OraMDAttribute) Property

- Value (OraMDAttribute) Property

- IsMDObject Property

Methods

None.

Examples

See "Schema Objects Used in OraMetaData Examples" for `OraMetaData` Schema Definitions used in these examples.

Example: Describing a Table

See "Describing a Table Example".

Example: Describing a User-Defined Type

See "Example: Describing a User-Defined Type".

Example: Describing Unknown Schema Objects

See "Example: Describing Unknown Schema Objects".

See Also: OraMetaData Object Describe Method

## OraMetaData Object

Description

The `OraMetaData` object is returned by invoking the `Describe` method of the `OraDatabase` interface. The `Describe` method takes the name of a schema object, such as the `emp` table, and returns an `OraMetaData` object. The `OraMetaData` object provides methods for dynamically navigating and accessing all the attributes (`OraMDAttribute` collection) of a schema object described.

An `OraMetaData` object is a collection of `OraMDAttribute` objects that represent the description information about a particular schema object in the database. The following table is an example of attributes for a `OraMetaData` object of type table (`ORAMD_TABLE)`.

Table 9-2 list the `ORAMD_TABLE` attributes.

Table 9-2 ORAMD_TABLE Attributes

Attribute Name | Value Type | Description
--- | --- | ---
`ObjectID` | `Integer` | Object ID.
`NumCols` | `Integer` | Number of columns.
`ColumnList` | `OraMetaData` | Column list.
`IsTyped` | Boolean | Is the table typed?
`IsTemporary` | Boolean | Is the table temporary?
`Duration` | `String` | Duration - can be session, transaction, or null.
`DBA` | `Integer` | Data block address of the segment header.
`TableSpace` | `Integer` | Tablespace in which the table resides.
`IsClustered` | Boolean | Is the table clustered?
`IsPartitioned` | Boolean | Is the table partitioned?
`IsIndexOnly` | Boolean | Is the table index-only?

See Also: "Type (OraMetaData) Property"

Remarks

The `OraMetaData` object can be visualized as a table with three columns:

- Metadata attribute name

- Metadata attribute value

- Flag specifying whether the `Value` is another `OraMetaData` object

The `OraMDAttribute` objects contained in the `OraMetaData` object can be accessed by creating a subscript that uses ordinal integers or by using the name of the property. Referencing a subscript that is not in the collection (`0` to `Count-1`) results in the return of a `NULL` `OraMDAttribute` object.

Properties

- Count (OraMetaData) Property

- Type (OraMetaData) Property

Methods

- Attribute (OraMetaData) Method

Examples

See "Schema Objects Used in OraMetaData Examples" for `OraMetaData` schema definitions used in these examples.

The following Visual Basic example illustrates a simple use of this facility. It retrieves and displays several attributes of the `emp` table.

    Set empMD = OraDatabase.Describe("emp")

    'Display the name of the Tablespace
    msgbox empMD("tablespace")

    'Display name, data type, and size of each column in the emp table.
    Set empColumnsMD = empMD("Columns")
    for I = 1 to empColumns.Count
       Set ColumnMD = empColumnsMD(I)
       MsgBox ColumnMD("Name") & ColumnMD("Data Type") & ColumnMD("Length")
    Next I

Example: Describing a User-Defined Type

See "Example: Describing a User-Defined Type"

Example: Describing Unknown Schema Objects

See "Example: Describing Unknown Schema Objects"

See Also: OraMDAttribute Object Describe Method

## OraNumber Object

Description

The `OraNumber` interface provides methods for operations on the Oracle `Number` data types. This interface exposes a set of math operations that provide greater precision than is available in some programming environments, such as Visual Basic.

Remarks

The `OraNumber` object can be obtained through the `CreateOraNumber` method of the `OraSession` object or by calling the `Clone` method on an existing `OraNumber`.

All of the methods of the `OraNumber` object that take a numeric argument accept a string, another numeric type, such as a `long` in Visual Basic, or another `OraNumber` object.

Note: If a Visual Basic numeric value (or constant) is used as an argument, it is limited to the maximum precision provided by the language.

The `OraNumber` on which the math operation is called holds the result of the operation (overwriting any previous value). If a `Format` was specified (through the `Format` property), the value of an `OraNumber` must match this format or an error is raised when the `Value` property is accessed.

Properties

- Format (OraNumber) Property

- Value (OraNumber) Property

Methods

- Abs Method

- Add (OraNumber) Method

- ArcCos (OraNumber) Method

- ArcSin (OraNumber) Method

- ArcTan (OraNumber) Method

- ArcTan2 (OraNumber) Method

- Ceil (OraNumber) Method

- Clone (OraNumber) Method

- Cos (OraNumber) Method

- Div (OraNumber) Method

- Exp (OraNumber) Method

- Floor (OraNumber) Method

- HypCos (OraNumber) Method

- HypSin (OraNumber) Method

- HypTan (OraNumber) Method

- IsEqual (OraNumber) Method

- IsGreater (OraNumber) Method

- IsLess (OraNumber) Method

- Ln (OraNumber) Method

- Log (OraNumber) Method

- Mod (OraNumber) Method

- Mul (OraNumber) Method

- Neg (OraNumber) Method

- Power (OraNumber) Method

- Round (OraNumber) Method

- SetPi (OraNumber) Method

- Sin (OraNumber) Method

- Sqrt (OraNumber) Method

- Sub (OraNumber) Method

- Tan (OraNumber) Method

- Trunc (OraNumber) Method

Example

A scientific calculator example program is included as part on the samples installed with Oracle Objects for OLE. See "Demonstration Schema and Code Examples".

See Also: OraSession Object

## OraObject Object

Description

The `OraObject` interface is a representation of an Oracle value instance (non-referenceable object instance or embedded objects). Value instances are instances of an Oracle object type stored in the column of a table or attribute of an another Oracle object instance or element of an Oracle collection.

Remarks

Implicitly an `OraObject` object contains a collection interface for accessing and manipulating (updating and inserting) individual attributes of an value instance. Individual attributes can be accessed by using a subscript or the name of the attribute.

The `OraObject` attribute index starts at `1`. The `Count` property returns the total number of attributes. Each attribute of the underlying value instance is represented as an `OraAttribute` object.

Attribute values are retrieved as variants. The `Variant` type of the attribute depends on the attribute type of the object. Attribute values can be null and can be set to `Null`. For object types `REF`, LOB, and collection, attribute values are returned as corresponding OO4O objects for that type.

The `CreateOraObject` method on the `OraDatabase` object returns the `OraObject` object. The value instance associated with this `OraObject` object is created in the client-side object cache.

For information about executing a member method of a value instance, see "Executing a Member Method of an Oracle Object Instance".

For information about initializing an `OraObject` object representing a value instance in OO4O or executing a member method of a value instance, see "Instantiating Oracle LOBs, Objects, and Collections".

Properties

- Count (OraObject/Ref) Property

- IsNull (OraObject) Property

- TypeName (OraObject and OraRef) Property

- Version (OraObject and Ref) Property

Methods

- Clone (OraObject/Ref) Method

Examples

See "Schema Objects Used in the OraObject and OraRef Examples" for schema descriptions used in examples of `OraObject`/`OraRef` objects.

Example: Accessing Attributes of an OraObject Object

The following example accesses the attributes of the `ADDRESS` value instance in the database.

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim Address as OraObject

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb",  "scott/tiger", 0&)

    'create a dynaset object from person_tab
    set OraDynaset = OraDatabase.CreateDynaset("select * from person_tab",0&)

    'retrieve a address column from person_tab. Here Value property of OraField
    'object returns Address OraObject
    set Address = OraDynaset.Fields("Addr").Value

    'access the attribute by dot notation
    msgbox Address.Street

    'access the attribute using '!' notation ( early binding application)
    msgbox Address!Street

    'access the attribute by index
    msgbox Address(1)

    'access the attribute by name
    msgbox Address("Street")

    'access all the attributes of Address OraObject in the dynaset
    Do Until OraDynaset.EOF
        For index = 1 To Address.Count
            msgbox Address(index)
        Next Index
    OraDynaset.MoveNext
    Loop

Example: Updating Attributes of an OraObject Object

The following examples modify the attributes of the `ADDRESS` value instance in the database.

Dynaset Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim Address as OraObject

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from person_tab
    set OraDynaset = OraDatabase.CreateDynaset("select * from person_tab", 0&)

    'retrieve a address column from person_tab.
    'Here Value property of OraField object returns Address OraObject

    set Address = OraDynaset.Fields("Addr").Value

    'start the Edit operation and modify the Street attribute
    OraDynaset.Edit
    Address.Street  =  "Oracle Parkway"
    OraDynaset.Update

Parameter Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim Address as OraObject

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create an  OraParameter object represent Address object bind Variable
    OraDatabase.Parameters.Add "ADDRESS", Empty, ORAPARM_INPUT, ORATYPE_OBJECT, _
                        "ADDRESS"

    'get the uninitialized 'Empty' Address object from OraParameter
    set Address = OraDatabase.Parameters("ADDRESS").Value

    'modify the 'Street' attribute of the Address
    Address.Street = "Oracle Parkway"

    'execute the sql statement which updates Address in the person_tab
    OraDatabase.ExecuteSQL ("update person_tab set addr = :ADDRESS where age = 40")

Example: Inserting an OraObject Object

The following examples insert a new field (value instance) called `ADDRESS` in the database.

Dynaset Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim AddressNew as OraObject

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from person_tab
    set OraDynaset = OraDatabase.CreateDynaset("select * from person_tab", 0&)

    ' create a new Address object in OO4O
    set AddressNew = OraDatabase.CreateOraObject("ADDRESS")

    'initialize the Address object attribute to new value
    AddressNew.Street = "Oracle Parkway"
    AddressNew.State = "CA"

    'start the dynaset AddNew operation and set the Address field to new address
    ' value
    OraDynaset.Addnew
    OraDynaset.Fields("ADDR").Value = AddressNew
    OraDynaset.Update

OraParameter Example

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim AddressNew as OraObject

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create an  OraParameter object represent Address object bind Variable
    OraDatabase.Parameters.Add "ADDRESS", Null, ORAPARM_INPUT, ORATYPE_OBJECT, _
                         "ADDRESS"

    ' create a new Address object in OO4O
    set AddressNew = OraDatabase.CreateObject("ADDRESS")

    'initialize the Address object attribute to new value
    AddressNew.Street = "Oracle Parkway"
    AddressNew.State = "CA"

    'set the Address to ADDRESS parameter
    Oradatabase.Parameters("ADDRESS").Value = AddressNew

    'execute the sql statement which updates Address in the person_tab
    OraDatabase.ExecuteSQL ("insert into person_tab values (30,'Eric',:ADDRESS))

See Also: "Oracle Object Data Types" for information on support of Oracle object-relational features OraParameter Object OraParamArray Object OraRef Object OraAttribute Object CreateOraObject (OraDatabase) Method "Executing a Member Method of an Oracle Object Instance"

## OraParamArray Object

Description

An `OraParamArray` object represents an array type bind variable in a SQL statement or PL/SQL block, as opposed to a scalar type bind variable represented by the `OraParameter` object.

Remarks

`OraParamArray` objects are created, accessed, and removed indirectly through the `OraParameters` collection of an `OraDatabase` object. Each parameter has an identifying name and an associated value.

Implicitly an `OraParamArray` object contains an OLE automation collection interface for accessing and manipulating individual elements of an array. Individual elements can be accessed using a subscript or the `Get_Value` method. Individual elements can be modified by using a subscript or the `Put_Value` method.

Element values are retrieved as `Variant` types. The `Variant` type of the element depends on the `ServerType` of the `OraParamArray` object. Element values can be null and can be set to `Null`. For elements of type objects and `REF`s, element values are returned as corresponding OO4O objects for that type.

You can automatically bind a parameter to SQL and PL/SQL statements of other objects (as noted in the objects descriptions) by using the name of the parameter as a placeholder in the SQL or PL/SQL statement. Using parameters can simplify dynamic queries and increase program performance. Parameters are bound to SQL statements and PL/SQL blocks before execution.

The `OraParameters` collection is part of the `OraDatabase` object so that all parameters are available to any SQL statement or PL/SQL block executed within the database (through `CreateDynaset`, `ExecuteSQL`, or `CreateSQL` methods). Before a SQL statement or PL/SQL block is executed, an attempt is made to bind all parameters of the associated `OraDatabase` object. The bindings that fail (because the parameter does not apply to that particular SQL statement or PL/SQL block) are noted and no attempt is made to bind them again if the SQL statement or PL/SQL block is reexecuted but does not change.

Because neither SQL statements nor PL/SQL blocks are parsed locally (all parsing is done by Oracle Database), any unnecessary binding results in performance degradation. To prevent unnecessary parameter binding, use the `AutoBindDisable` and `AutoBindEnable` methods.

Properties

- ArraySize Property

- LastErrorText Property

- MinimumSize Property

- Name Property

- ServerType Property

- Status Property

- Type Property

Methods

- AutoBindDisable Method

- AutoBindEnable Method

- Get_Value Method

- Put_Value Method

Example

Example: Using OraParamArrays with SQL Statements

The following example shows how to use the `OraParamArray` object with SQL statements:

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraSqlStmt As OraSQLStmt
    Dim PartNoArray As OraParamArray
    Dim DescArray As OraParamArray
    Dim I As Integer

    'Test case for inserting/updating/deleting multiple rows using parameter

    ' arrays with SQL statements
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase("exampledb", "scott/tiger",  0&)

    'Create table
    OraDatabase.ExecuteSQL ("create table part_nos(partno number," & _
                             "description char(50), primary key(partno))")
    OraDatabase.Parameters.AddTable "PARTNO", ORAPARM_INPUT, ORATYPE_NUMBER, 10, 22
    OraDatabase.Parameters.AddTable "DESCRIPTION", ORAPARM_INPUT, _
                               ORATYPE_CHAR, 10, 50
    Set PartNoArray = OraDatabase.Parameters("PARTNO")
    Set DescArray = OraDatabase.Parameters("DESCRIPTION")

     'Initialize arrays
    For I = 0 To 9
      achar = "Description" + Str(I)
      PartNoArray(I) = 1000 + I
      DescArray(I) = achar
    Next I
    Set OraSqlStmt = OraDatabase.CreateSql("insert into
    part_nos(partno, description) values(:PARTNO,:DESCRIPTION)", 0&)

    'Update the newly created part_nos table
    For I = 0 To 9
      achar = "Description" + Str(1000 + I)
      DescArray(I) = achar
    Next I

    'Update table
    Set OraSqlStmt = OraDatabase.CreateSql("update part_nos set DESCRIPTION" & _
                               "=:DESCRIPTION where PARTNO = :PARTNO", 0&)

    'Deleting rows
    Set OraSqlStmt = OraDatabase.CreateSql("delete  from  part_nos where" & _
                               "DESCRIPTION=: Description ", 0&)

    'Drop the table
    OraDatabase.ExecuteSQL ("drop table part_nos")

Example: Using OraParamArrays with PL/SQL

The following is an example using `OraParamArray` objects with PL/SQL. The `Employee` PL/SQL package can be set up with the `ORAEXAMP.SQL` script. See "Demonstration Schema and Code Examples".

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim EmpnoArray As OraParamArray
    Dim EnameArray As OraParamArray

    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase("exampledb", "scott/tiger", 0&)
    OraDatabase.Parameters.Add "ArraySize", 3, ORAPARM_INPUT
    OraDatabase.Parameters.AddTable "EMPNOS", ORAPARM_INPUT, ORATYPE_NUMBER,3, 22
    OraDatabase.Parameters.AddTable "ENAMES", ORAPARM_OUTPUT, _
                                     ORATYPE_VARCHAR2, 3, 10
    Set EmpnoArray = OraDatabase.Parameters("EMPNOS")
    Set EnameArray = OraDatabase.Parameters("ENAMES")

    'Initialize the newly created input parameter table EMPNOS
    EmpnoArray(0) = 7698
    EmpnoArray(1) = 7782
    EmpnoArray(2) = 7654

    'Execute the PLSQL package
    OraDatabase.ExecuteSQL ("Begin Employee.GetEmpNamesInArray(:ArraySize," & _
                             ":EMPNOS, :ENAMES); End;")
    'Print out Enames
    MsgBox EnameArray(0)
    MsgBox EnameArray(1)
    MsgBox EnameArray(2)

See Also: OraParameters Collection OraParameter Object

## OraParameter Object

Description

An `OraParameter` object represents a bind variable in a SQL statement or PL/SQL block.

Remarks

`OraParameter` objects are created, accessed, and removed indirectly through the `OraParameters` collection of an `OraDatabase` object. Each parameter has an identifying name and an associated value. You can automatically bind a parameter to SQL and PL/SQL statements of other objects (as noted in the object descriptions), by using the parameter name as a placeholder in the SQL or PL/SQL statement. Using parameters can simplify dynamic queries and increase program performance.

Parameters are bound to SQL statements and PL/SQL blocks before execution. In the case of a SQL `SELECT` statement, binding occurs before dynaset creation.

The `OraParameters` collection is part of the `OraDatabase` object. Therefore, all parameters are available to any SQL statement or PL/SQL block executed within the database (through the `CreateDynaset` or `ExecuteSQL` methods).

Before a SQL statement or PL/SQL block is executed, an attempt is made to bind all parameters of the associated `OraDatabase` object. The bindings that fail (because the parameter does not apply to that particular SQL statement or PL/SQL block), are noted and no attempt is made to bind them again if the SQL statement or PL/SQL block is reexecuted but does not change.

Because neither SQL statements nor PL/SQL blocks are parsed locally (all parsing is done by Oracle Database), any unnecessary binding results in performance degradation. To prevent unnecessary parameter binding, use the `AutoBindDisable` and `AutoBindEnable` methods.

By default, the maximum size of the `ORAPARM_OUTPUT` variable for `ServerType` `CHAR` and `VARCHAR2` is set to 127 bytes. Use the `MinimumSize` property to change this value. The minimum size of an `ORAPARM_OUTPUT` variable for `CHAR`, `VARCHAR2`, and `ORATYPE_RAW_BIN` must always be greater than the size of the expected data from the database column.

`ServerType` `ORATYPE_RAW_BIN` is used when binding to Oracle `Raw` columns. A byte array is used to put or get values. The maximum allowable size of `ORATYPE_RAW_BIN` bind buffers is 2000 bytes when bound to a column of a table, 32 KB when bound to a stored procedure. For example code, see the samples in the `ORACLE_BASE\ORACLE_HOME``\OO4O\VB\Raw` directory.

Properties

- DynasetOption Property

- MinimumSize Property

- Name Property

- ServerType Property

- Status Property

- Type Property

- Value Property

Methods

- AutoBindDisable Method

- AutoBindEnable Method

- DynasetCacheParams Method

See Also: CreateDynaset Method DynasetOption Property ExecuteSQL Method OraDatabase Object OraParameters Collection

## OraRef Object

Description

The `OraRef` interface represents an Oracle `REF` (reference) as well as a referenceable object (standalone instance).

Remarks

An Oracle `REF` is an identifier to a referenceable object. Referenceable objects are stored in rows of an object table. By pinning a `REF` object, referenceable objects are fetched to the client side. An `OraRef` object implicitly pins the underlying `REF` when the attributes of a referenceable object are accessed for the first time. The `OraRef` also encapsulates the functionality for an object navigational operation utilizing the Complex Object Retrieval Capability (COR).

Attributes of a referenceable object represented by the `OraRef` object are accessed in the same manner as attributes of an value instance represented by the `OraObject` interface. When pinned, `OraRef` contains an `OraObject` interface through the containment mechanism in COM. At run time, the `OraRef` interface can be typecast to the `OraObject` interface.

`OraRef` provides methods for update and delete operations on a referenceable object, independent of the context from which they originated, such as dynasets, parameters, and so on.

An object-level lock should be obtained before modifying the attributes of a referenceable object. This is done though the `Edit` method of the `OraRef`object.

The `CreateOraObject` method on the `OraDatabase` object creates a new referenceable object in the database and returns information associated with the `OraRef` Object. The `CreateOraObject` and `Update` methods pair inserts a new referenceable object in the database.

For information about initializing an `OraRef` object representing a referenceable object in OO4O or executing a member method of a referenceable object, see "Instantiating Oracle LOBs, Objects, and Collections".

Properties

- Count (OraObject/Ref) Property

- EditOption (OraRef) Property

- HexValue (OraRef) Property

- IsRefNull (OraRef) Property

- PinOption (OraRef) Property

- TableName (OraRef) Property

- TypeName (OraObject and OraRef) Property

- Version (OraObject and Ref) Property

Methods

- CancelEdit (OraRef) Method

- Clone (OraObject/Ref) Method

- Delete (OraRef) Method

- Edit (OraRef) Method

- Refresh (OraRef) Method

- Update (OraRef) Method

Examples

Before running the sample code, make sure that you have the necessary data types and tables in the database. See "Schema Objects Used in the OraObject and OraRef Examples" for schema descriptions used in examples of `OraObject`/`OraRef`.

Example: Pinning Ref Values

The following example pins the attributes of the `PERSON` referenceable object in the database.

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim Person as OraRef

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from customers
    set OraDynaset = OraDatabase.CreateDynaset("select * from customers", 0&)

    'retrieve a aperson column from customers. Here Value property of
    ' OraField object returns Person OraRef
    set Person = OraDynaset.Fields("aperson").Value

    'access the attribute of person. This operation pins the Person ref
    'value and fetches the Person referenceable object to the client.
    msgbox Person.Name

Example: Accessing Attribute Values

The following example accesses the attributes of the `PERSON` referenceable object in the database.

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim OraDynaset as OraDynaset
    Dim Person as OraRef
    Dim Address as OraObject

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'create a dynaset object from customers
    set OraDynaset = OraDatabase.CreateDynaset("select * from customers", 0&)

    'retrieve a aperson column from customers. Here Value property of OraField
    'object returns Person OraRef
    set Person = OraDynaset.Fields("aperson").Value

    'access the attribute by dot notation.
    msgbox Person.Name

    'access the attribute using '!' notation ( early binding application)
    msgbox Person!Name

    'access the attribute by index
    msgbox Person(1)

    'access the attribute by name
    msgbox Person("Name")

    'access Addr attribute . This returns Address OraObject.
    set Address = Person.Addr

Example: Updating Attribute Values

The following example updates the attributes of the `PERSON` referenceable object in the database.

Dynaset Example

See "Updating Attribute Values: Dynaset Example".

Parameter Example

See "Updating Attribute Values: Parameter Example".

Example: Inserting Referenceable Objects

The following example inserts the new `PERSON` referenceable object in the database.

    Dim OraSession as OraSession
    Dim OraDatabase as OraDatabase
    Dim Person  as OraRef

    'Create the OraSession Object.
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    'Create the OraDatabase Object by opening a connection to Oracle.
    Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

    'CreateOraObject   creates a new referenceable object in the PERSON_TAB object
    'table and returns associated OraRef
    set Person = OraDatabase.CreateOraObject("PERSON","PERSON_TAB")

    'modify the attributes of Person
    Person.Name = "Eric"
    Person.Age = 35

    'Update method inserts modified referenceable object in the PERSON_TAB.
    Person.Update

See Also: OraObject Object OraParameter Object OraParamArray Object

## OraServer Object

Description

The `OraServer` interface represents a physical network connection to an Oracle database.

Remarks

The `OraServer` interface exposes the connection multiplexing feature provided in the Oracle Call Interface. After an `OraServer` object is created, multiple user sessions (`OraDatabase`) can be attached to it by invoking the `OpenDatabase` method. This feature is particularly useful for application components, such as Internet Information Server (IIS), that use Oracle Objects for OLE in n-tier distributed environments. The use of connection multiplexing when accessing Oracle databases with a large number of user sessions active can help reduce server processing and resource requirements while improving the database scalability.

As illustrated in Figure 9-1, the `OraServer` interface contains a connection to an Oracle database and provides a method (`OpenDatabase`) for creating user sessions (`OraDatabase` objects) on the database connection it contains.

Figure 9-1 OraServer to Oracle Database Relationship

Description of the illustration o4o00015.gif

Properties

- Name Property

- Session Property

- Databases Property

Methods

- ChangePassword (OraServer) Method

- OpenDatabase Method

- Open (OraServer) Method

See Also: OraConnection Object OraDatabase Object OraClient Object OraDynaset Object

## OraSession Object

Description

An `OraSession` object manages collections of `OraDatabase`, `OraConnection`, and `OraDynaset` objects used within an application.

Remarks

Typically, a single `OraSession` object is created for each application, but you can create named `OraSession` objects for shared use within and between applications.

The `OraSession` object is the highest level object for an application. `OraSession` and `OraServer` objects are the only objects created by the `CreateObject` Visual Basic or Visual Basic for Applications APIs and not by an Oracle Objects for OLE method.

Properties

- Client Property

- Connections Property

- LastServerErr Property

- LastServerErrText Property

- Name Property

- OIPVersionNumber Property

- DbPoolCurrentSize Property

- DbPoolInitialSize Property

- DbPoolMaxSize Property

Methods

- BeginTrans Method

- ChangePassword (OraSession) Method

- CommitTrans Method

- ConnectSession Method

- CreateDatabasePool Method

- CreateNamedSession Method

- CreateOraIntervalDS Method

- CreateOraIntervalYM Method

- CreateOraNumber Method

- CreateOraTimeStamp Method

- CreateOraTimeStampTZ Method

- DestroyDatabasePool Method

- GetDatabaseFromPool Method

- OpenDatabase Method

- LastServerErrReset Method

- ResetTrans Method

- Rollback Method

Examples

The following code fragments show how to create an `OraSession` object:

    Dim oo4oSession as Object
    Set oo4oSession = CreateObject("OracleInProcServer.XOraSession")

or

    Dim oo4oSession as New OraSessionClass

or

    Dim oo4oSession as OraSession
    Set oo4oSession = New OraSessionClass

See Also: OraClient Object OraConnection Object OraDatabase Object OraDynaset Object OraServer Object

## OraSQLStmt Object

Description

An `OraSQLStmt` object represents a single SQL statement. Use the `CreateSQL` method to create the `OraSQLStmt` object from an `OraDatabase` object.

During create and refresh operations, `OraSQLStmt` objects automatically bind all relevant, enabled input parameters to the specified SQL statement, using the parameter names as placeholders in the SQL statement. This can improve the performance of SQL statement execution without parsing the SQL statement again.

Properties

- Connection Property

- Database Property

- Options Property

- RecordCount Property

- Session Property

- SQL Property

- NonBlockingState Property

Methods

- Refresh Method

- Cancel Method

- Close Method

See Also: CreateSQL Method OraParameter Object "Asynchronous Processing"

## OraSubscription Object

Description

An `OraSubscription` object that represents the subscription to a database event.

Remarks

`OraSubscription` objects are created, accessed, and removed indirectly through the `OraSubscriptions` collection of an `OraDatabase` object. Each subscription has a name that associates with an Oracle database event.

The `OraSubscriptions` collection is part of the `OraDatabase` object.

Properties

- Name Property

Methods

- Register Method

- Unregister Method

See Also: OraDatabase Object OraSubscriptions Collection "Database Events"

## OraTimeStamp Object

Description

The `OraTimeStamp` object represents the Oracle `TIMESTAMP` and Oracle `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` data types and provides methods for operations on these two Oracle data types. The `OraTimeStamp` represents a date-time value that stores the following information: year, day, hour, minute, second, and nanosecond.

Remarks

The `OraTimeStamp` object is created by the `OraSession.OraCreateTimeStamp` method or by calling the `Clone` method on an existing `OraTimeStamp` object.

An `OraTimeStamp` object can be bound using `ServerType` `ORATYPE_TIMESTAMP` or `ORATYPE_TIMESTAMPLTZ`. This allows the binding of a value to a parameter associated with an Oracle `TIMESTAMP` or an Oracle `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` data type in a SQL or PL/SQL statement respectively.

When binding a string associated with a `TIMESTAMP` or a `TIMESTAMP` `WITH` `LOCAL` `TIME` `ZONE` data types, the `ServerType` must be specified to be a string type (for example, `ORATYPE_VARCHAR2`, `ORATYPE_STRING`) and the string must be in the format specified by the `NLS_TIMESTAMP_FORMAT`.

Properties

- Day (OraTimeStamp) Property

- Format (OraTimeStamp) Property

- Hour (OraTimeStamp) Property

- Minute (OraTimeStamp) Property

- Month (OraTimeStamp) Property

- Nanosecond(OraTimeStamp) Property

- Second (OraTimeStamp) Property

- Value (OraTimeStamp) Property

- Year (OraTimeStamp) Property

Methods

- AddIntervalDS Method

- AddIntervalYM Method

- Clone (OraTimeStamp) Method

- IsEqual (OraTimeStamp) Method

- IsGreater (OraTimeStamp) Method

- IsLess (OraTimeStamp) Method

- ToDate Method

- ToOraTimeStampTZ Method

See Also: CreateOraTimeStamp Method OraNumber Object ServerType Property

## OraTimeStampTZ Object

Description

The `OraTimeStampTZ` object represents an Oracle `TIMESTAMP` `WITH` `TIME` `ZONE` data type and provides methods for operations on this Oracle data type. The `OraTimeStampTZ` represents a date-time value in a specific time zone that stores the following information: year, day, hour, minute, second, nanosecond, and the time zone.

Remarks

The `OraTimeStampTZ` object is created by the `OraSession.OraCreateTimeStampTZ` method or by calling the `Clone` method on an existing `OraTimeStampTZ` object.

An `OraTimeStampTZ` object can be bound using `ServerType` `ORATYPE_TIMESTAMPTZ`. This allows the binding of a value to a parameter associated with an Oracle `TIMESTAMP` `WITH` `TIME` `ZONE` data type in a SQL or PL/SQL statement.

When binding a string associated with an `TIMESTAMP` `WITH` `TIME` `ZONE` data type, the `ServerType` must be specified to be a string type (for example, `ORATYPE_VARCHAR2`, `ORATYPE_STRING`) and the string must be in the format specified by `NLS_TIMESTAMP_TZ_FORMAT`.

Properties

- Day (OraTimeStampTZ) Property

- Format (OraTimeStampTZ) Property

- Hour (OraTimeStampTZ) Property

- Minute (OraTimeStampTZ) Property

- Month (OraTimeStampTZ) Property

- Nanonsecond (OraTimeStampTZ) Property

- Second (OraTimeStampTZ) Property

- TimeZone (OraTimeStampTZ) Property

- Value (OraTimeStampTZ) Property

- Year (OraTimeStampTZ) Property

Methods

- AddIntervalDS Method

- AddIntervalYM Method

- Clone (OraTimeStampTZ) Method

- IsEqual (OraTimeStampTZ) Method

- IsGreater (OraTimeStampTZ) Method

- IsLess (OraTimeStampTZ) Method

- ToDate Method

- ToOraTimeStamp Method

- ToOraTimeStampLTZ Method

- ToUniversalTime Method

See Also: CreateOraTimeStampTZ Method OraNumber Object ServerType Property

## OraConnections Collection

Description

The `OraConnections` collection maintains a list of `OraConnection` objects. The list is not modifiable; you cannot add to or remove from this collection.

Remarks

You can access the `OraConnection` objects in this collection by creating a subscript (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of `OraConnection` objects in the collection by using the `Count` property. Referencing a subscript that is not within the collection (`0` to `Count-1`) results in the return of a `NULL` `OraConnection` object.

Properties

- Count Property

Methods

None.

See Also: OraConnection Object

## OraFields Collection

Description

The `OraFields` collection maintains a list of the `OraField` objects. The list is not modifiable; you cannot add to or remove from this collection.

Remarks

You can access the `OraField` objects in this collection by creating a subscript (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of `OraField` objects in the collection by using the `Count` property. Referencing a subscript that is not within the collection (`0` to `Count-1`) results in the return of a null `OraField` object.

Properties

- Count Property

Methods

- OriginalItem Method

See Also: OraField Object

## OraParameters Collection

Description

The `OraParameters` collection maintains a list of `OraParameter` objects. Unlike the other collection objects, this list is modifiable; you can add to and remove from the collection.

Remarks

You can access the `OraParameter` objects in this collection by creating a subscript (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of `OraParameter` objects in the collection by using the `Count` property. Referencing a subscript that is not within the collection (`0` to `Count-1`) results in the return of a null `OraParameter` object.

In addition to accessing the `OraParameter` objects of the collection, you can use the collection to create and destroy parameters by using the `Add` and `Remove` methods, respectively.

Properties

- Count Property

Methods

- Add Method

- AddTable Method

- Remove Method

See Also: OraParameter Object OraParamArray Object

## OraSessions Collection

Description

The `OraSessions` collection maintains a list of `OraSession` objects. The list is not modifiable; you cannot add to or remove from this collection.

Remarks

You can access the `OraSession` objects in this collection by creating a subscript (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of `OraSession` objects in the collection by using the `Count` property. Referencing a subscript that is not within the collection (`0` to `Count-1`) results in the return of a null `OraSession` object.

Properties

- Count Property

Methods

None.

See Also: OraSession Object

## OraSubscriptions Collection

Description

The `OraSubscriptions` collection maintains a list of `OraSubscription` objects, which represent the subscription to a database event. Unlike the other collection objects, this list is modifiable; you can add to and remove from the collection.

Remarks

You can access the `OraSubscription` objects in this collection by creating a subscript (using ordinal integers) or by using the name the object was given at its creation. You can obtain the number of `OraSubscription` objects in the collection by using the `Count` property. Referencing a subscript that is not within the collection (`0` to `Count-1`) results in the return of a null `OraSubscription` object.

In addition to accessing the `OraSubscription` objects of the collection, you can use the collection to create and destroy subscriptions by using the `Add` and `Remove` methods, respectively.

Properties

- Count Property

Methods

- Add (OraSubscriptions Collection) Method

- Remove (OraSubscriptions Collection) Method

See Also: OraSubscription Object OraDatabase Object "Database Events"